import sqlite3


class Database:
    def __init__(self, path, converter):
        self.conn = sqlite3.connect(path)
        self.cursor = self.conn.cursor()
        self.converter = converter

    def create_table(self, start_index, end_index):
        self.cursor.execute("CREATE TABLE IF NOT EXISTS records ("
                            "  id INTEGER PRIMARY KEY,"
                            "  start_index INTEGER,"
                            "  start_convert TEXT,"
                            "  end_index INTEGER,"
                            "  end_convert TEXT,"
                            "  current_index INTEGER,"
                            "  current_convert TEXT,"
                            "  hit_index INTEGER,"
                            "  hit_convert TEXT"
                            ")")
        self.cursor.execute("CREATE TABLE IF NOT EXISTS failed_record ("
                            "  id INTEGER PRIMARY KEY,"
                            "  password_index INTEGER,"
                            "  password TEXT,"
                            "  type TEXT,"
                            "  code INTEGER,"
                            "  message TEXT"
                            ")")
        self.conn.commit()
        self.cursor.execute("SELECT * FROM records WHERE start_index=? AND end_index=?", (start_index, end_index - 1))
        result = self.cursor.fetchone()
        if result:
            return None
        start_convert = self.converter.to_string(start_index)
        end_convert = self.converter.to_string(end_index - 1)
        current_index = -1
        current_convert = ""
        hit_index = -1
        hit_convert = ""
        self.cursor.execute("INSERT INTO records ("
                            "  start_index,"
                            "  start_convert, "
                            "  end_index,"
                            "  end_convert, "
                            "  current_index,"
                            "  current_convert, "
                            "  hit_index, "
                            "  hit_convert"
                            ") VALUES (?,?,?,?,?,?,?,?)",
                            (
                                start_index,
                                start_convert,
                                end_index - 1,
                                end_convert,
                                current_index,
                                current_convert,
                                hit_index,
                                hit_convert
                            )
                            )
        self.conn.commit()

    def add_failed_record(self, password_index, password, error_type, code, message):
        self.cursor.execute(
            "INSERT INTO failed_record (password_index, password, type, code, message) VALUES (?,?,?,?,?)",
            (password_index, password, error_type, code, message)
        )
        self.conn.commit()

    def get_failed_records(self):
        self.cursor.execute("SELECT * FROM failed_record")
        results = self.cursor.fetchall()
        return [
            {
                'id': result[0],
                'password_index': result[1],
                'password': result[2],
                'type': result[3],
                'code': result[4],
                'message': result[5]
            } for result in results
        ]

    def query_record(self, start_index, end_index):
        self.cursor.execute(
            "SELECT * FROM records WHERE start_index=? AND end_index=?",
            (start_index, end_index - 1)
        )
        result = self.cursor.fetchone()
        if result:
            return {
                'id': result[0],
                'start_index': result[1],
                'start_convert': result[2],
                'end_index': result[3],
                'end_convert': result[4],
                'current_index': result[5],
                'current_convert': result[6],
                'hit_index': result[7],
                'hit_convert': result[8]
            }

    def update_record(self, start_index, end_index, current, hit=(None, None)):
        params = []
        values = []
        if current[0] is not None:
            params.append("current_index=?")
            params.append("current_convert=?")
            values.append(current[0])
            values.append(current[1])
        if hit[0] is not None:
            params.append("hit_index=?")
            params.append("hit_convert=?")
            values.append(hit[0])
            values.append(hit[1])
        params_str = ", ".join(params)
        self.cursor.execute("UPDATE records SET {} WHERE start_index=? AND end_index=?".format(params_str),
                            values + [start_index, end_index - 1])
        self.conn.commit()
